package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Employee;

public class DbEmployee implements IFDBEmployee {
	
	private Connection con;
	
	public DbEmployee()
	{
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<Employee> getAllEmployees() {
		return miscWhere("");
	}

	@Override
	public Employee getEmployeeByCPR(String CPR) {
		String wClause = " cpr = '" + CPR + "'";
		System.out.println("Search in Employee: " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public Employee getEmployeeByName(String name) {
		String wClause = " name = '" + name +"'";
		System.out.println("Search in Employee: " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public Employee getEmployeeById(int id) {
		String wClause = " id = '" + id + "'";
		System.out.println("Search in Employee: " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public int insertEmployee(Employee employee) throws Exception {
				//call to get the next id number
				int nextID = GetMax.getMaxId("Select max(id) from Contact");
				nextID = nextID + 1;
				System.out.println("next id = " +  nextID);
				int rc = -1;
				String insertQuery="INSERT INTO Contact(id, address, city, zipCode, phoneNo, eMail, type, status";
				String valueQuery = " VALUES('"+
						nextID  + "','"  +
						employee.getAddress() + "','"  +
						employee.getCity() + "','"  +
						employee.getZipCode() + "','"  +
						employee.getPhoneNO() + "','"  +
						employee.getEMail() + "','"  +
						employee.getStatus() + "'";

				String queryContact = insertQuery + ") " + valueQuery + ")";
				
				insertQuery="INSERT INTO Employee(id, cpr, name, surname, salaryPerHour, hoursPerWeek, role";
				valueQuery = " VALUES('"+
						nextID  + "','"  +
						employee.getCPR() + "','"  +
						employee.getName() + "','"  +
						employee.getSurname() + "','"  +
						employee.getSalaryPerHour() + "','"  +
						employee.getHoursPerWeek() + "','"  +
						employee.getRole() + "'";
				
				String queryEmployee = insertQuery + ") " + valueQuery + ")";

				System.out.println("insert Contact: " + queryContact);
				System.out.println("insert Employee: " + queryEmployee);
				try
				{ // insert new contact + employee
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(queryContact);
					rc = stmt.executeUpdate(queryEmployee);
					stmt.close();
				}//end try
				catch(SQLException ex){
					System.out.println("Employee not created");
					throw new Exception ("Employee is not inserted correctly");
				}
				return(rc);
			}

	@Override
	public int updateEmployee(Employee employee) {
		int rc=-1;

		String queryContact="UPDATE Contact SET "+
				"address = '" + employee.getAddress() + "', "+
				"city = '" + employee.getCity() + "', " +
				"zipCode = '" + employee.getZipCode() + "', " +
				"phoneNo = '" + employee.getPhoneNO() + "', " +
				"eMail = '" + employee.getEMail() + "', " +
				"status = '" + employee.getStatus() + "'" +
				" WHERE id = '"+ employee.getId() + "'";
		
		String queryEmployee="UPDATE Employee SET " +
				"cpr = '" + employee.getCPR() + "', " +
				"name = '" + employee.getName() + "', " +
				"surname = '" + employee.getSurname() + "', " +
				"salaryPerHour = '" + employee.getSalaryPerHour() + "', " +
				"hoursPerWeek = '" + employee.getHoursPerWeek() + "' " +
				"WHERE id = '" + employee.getId() + "'";
		
		System.out.println("Update Contact: " + queryContact);
		try
		{ //Update Employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryContact);
			rc = stmt.executeUpdate(queryEmployee);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Update exception in Employee db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteEmployee(Employee employee) {
		int rc=-1;

		String query="DELETE FROM Employee WHERE id = '" +
				employee.getId() + "'";
		System.out.println(query);
		try
		{ // delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in Employee db: "+ex);
		}
		return(rc);
	}

	private ArrayList<Employee> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Employee> list = new ArrayList<Employee>();	

		String query =  buildQuery(wClause);

		try{ // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while(results.next()){
				Employee employeeObj = new Employee();
				employeeObj = buildEmployee(results);	
				list.add(employeeObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
		//Singlewhere to select a employee 	
		private Employee singleWhere(String wClause)
		{
			ResultSet results;
			Employee employeeObj = new Employee();
			
				String query =  buildQuery(wClause);
				System.out.println(query);
				try{ // read the employee from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						employeeObj = buildEmployee(results);
						stmt.close();
					}		
					else{ //no employee was found
						employeeObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception: "+e);
				}

			return employeeObj;
		}
		
		//method to build the query for Employee
		private String buildQuery(String wClause)
		{
			String query="SELECT c.id, address, city, zipCode, phoneNo, eMail, status, "
					+ "cpr, name, surname, salaryPerHour, hoursPerWeek, role FROM Contact c, Employee e "
					+ "WHERE e.id = c.id";

			if (wClause.length()>0)
				query=query+" AND "+ wClause;

			return query;
		}
		
		//method to build a Employee object
		private Employee buildEmployee(ResultSet results)
		{   
			Employee employeeObj = new Employee();
			try{ // the columns from the table Employee and Contact are used
				employeeObj.setId(results.getInt("id"));
				employeeObj.setAddress(results.getString("address"));
				employeeObj.setCity(results.getString("city"));
				employeeObj.setZipCode(results.getString("zipCode"));
				employeeObj.setPhoneNO(results.getString("phoneNo"));
				employeeObj.setEMail(results.getString("eMail"));
				employeeObj.setStatus(results.getString("status"));
				employeeObj.setCPR(results.getString("cpr"));
				employeeObj.setName(results.getString("name"));
				employeeObj.setSurname(results.getString("surname"));
				employeeObj.setSalaryPerHour(results.getDouble("salaryPerHour"));
				employeeObj.setHoursPerWeek(results.getDouble("hoursPerWeek"));
				employeeObj.setRole(results.getString("role"));
			}
			catch(Exception e)
			{
				System.out.println("error in building the employee object");
			}
			return employeeObj;
		}
}
